# This is a sample Python script.s

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import requests
from bs4 import BeautifulSoup
import pandas as pd


def get_html(url):
    try:
        r = requests.get(url)
        r.raise_for_status()
        r.encoding = r.apparent_encoding
        return r.text
    except:
        return "失败"


def get_university_rank(ulist, html):
    soup = BeautifulSoup(html, 'html.parser')
    # 假设表格数据在<tbody>的<tr>标签中，具体要根据实际HTML结构调整
    trs = soup.find('tbody').find_all('tr')
    for tr in trs:
        info_list = []
        for td in tr.find_all('td'):  # 假设信息在<td>标签中，而非<th>
            ts = td.get_text(strip=True)
            if ts:  # 判断非空字符串
                info_list.append(ts)
        ulist.append(info_list)



if __name__ == "__main__":
    f985university_list = []  # 重命名变量以避免与内置list冲突
    url1= "https://gaokao.eol.cn/e_html/gk/2022/985yx/index.html"
    html_content = get_html(url1)
    if html_content != "失败":
        get_university_rank(f985university_list, html_content)
        df1 = pd.DataFrame(f985university_list)
        # 输出为Excel文件，确保已安装openpyxl库
        df1.to_excel('f985university.xlsx', index=False, engine='openpyxl')
        print("数据已保存为Excel文件:985university.xlsx")
    else:
        print("获取HTML失败")
    url2="https://gaokao.eol.cn/e_html/gk/2022/211yx/index.html"
    f211university_list = []
    uli_yifenyiduan = get_html(url2)
    if uli_yifenyiduan != "失败":
        get_university_rank(f211university_list, uli_yifenyiduan)
        df2 = pd.DataFrame(f211university_list)
        # 输出为Excel文件，确保已安装openpyxl库
        df2.to_excel('f211university.xlsx', index=False, engine='openpyxl')
        print("数据已保存为Excel文件:211university.xlsx")
    else:
        print("获取HTML失败")

#将两个Excel文件合成一个
# 读取第二个Excel文件到DataFrame
df1 = pd.read_excel('f985university.xlsx')

# 读取第二个Excel文件到DataFrame
df2 = pd.read_excel('f211university.xlsx')

# 创建一个ExcelWriter对象，用于写入数据到新的Excel文件
with pd.ExcelWriter('2309050107url.xlsx') as writer:
    # 将df1写入到名为'Sheet1'的sheet中
    df1.to_excel(writer, sheet_name='中国985大学与优势专业', index=False)
    # 将df2写入到名为'Sheet2'的sheet中
    df2.to_excel(writer, sheet_name='中国211大学与优势专业', index=False)